﻿CREATE VIEW spm.SsasPartitions
AS 
/*
 * Copyright (c) 2015 Dr. John Tunnicliffe, Decision Analytics
 * You are free to use this software under the terms of the following licence: https://SSASPartitionManager.codeplex.com/license
 *
 * View to support the SSAS Partition Manager.  See https://SSASPartitionManager.codeplex.com/documentation for documentation.
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SSASPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
WITH SsasPartitions AS (
	SELECT 
		A.SsasDatabaseName,
		A.SsasModelType,
		A.SsasCubeName,
		A.SsasMeasureGroupName,	
		P.PartitionSuffix,
		A.PartitionPeriod,
		A.DateColumnName,
		A.DateColumnDataType,
		P.PeriodStartDate,
		P.PeriodEndDate,
		year(P.PeriodStartDate) * 10000 + month(P.PeriodStartDate) * 100 + day(P.PeriodStartDate) as PeriodStartDateKey,
		year(P.PeriodEndDate) * 10000 + month(P.PeriodEndDate) * 100 + day(P.PeriodEndDate) as PeriodEndDateKey,
		P.PartitionSliceMember,
		A.PartitionSliceIsRange,
		P.PartitionSliceKey
	FROM spm.SsasPartitionDefinitions as A
		CROSS JOIN spm.SsasPartitionPeriods as P
	WHERE 
		A.PartitionPeriod = P.PartitionPeriod
		AND PeriodStartDate BETWEEN PartitionStartDate AND GETUTCDATE()
),
WithDateRange AS
(
	-- obtain the full range of dates between the PeriodStartDateKey and PeriodEndDateKey so we can generate a MDX range if required for our Multidimensional cubes
	SELECT 
		DailyPartitionSliceKey,
		DailyPartitionSliceMember,
		SsasDatabaseName,
		SsasCubeName,
		SsasMeasureGroupName,
		PeriodStartDateKey,
		PeriodEndDateKey,
		D.DateKey
	from SsasPartitions as A		
		join spm.SsasDateDimWrapper as D on  
			D.DateKey between A.PeriodStartDateKey and A.PeriodEndDateKey
)
SELECT 
	SsasDatabaseName,
	SsasModelType,
	SsasCubeName,
	SsasMeasureGroupName,
	SsasMeasureGroupName + N' ' + PartitionSuffix as PartitionName,
	PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	case
		when SsasModelType = N'Multidimensional' then 
			case
			-- period partition slice
			when PartitionSliceMember is not null and PartitionSliceIsRange = 0 
				then N'{' + PartitionSliceMember + N'.' + PartitionSliceKey + N'}'

			-- range partition slice
			when PartitionSliceMember is not null and PartitionSliceIsRange = 1 
				then replace(N'{' + 
					-- we use stuff to remove the first comma in the range
					stuff(
					(
						select 
							N',' + DailyPartitionSliceMember + N'.' + DailyPartitionSliceKey
						from WithDateRange as B		-- contains the full range of dates between the PeriodStartDateKey and PeriodEndDateKey so we can generate a MDX range if required
						where A.SsasDatabaseName = B.SsasDatabaseName
							and A.SsasCubeName = B.SsasCubeName
							and A.SsasMeasureGroupName = B.SsasMeasureGroupName
							and A.PeriodStartDateKey = B.PeriodStartDateKey
							and A.PeriodEndDateKey = B.PeriodEndDateKey
						order by B.DateKey
						for xml path('')
					), 1, 1, '') + N'}',
				N'&amp;',N'&')
			end 
		else null
	end as PartitionSlice,
	case lower(DateColumnDataType)
		when N'int' then
           -- for integer fields use between 
           DateColumnName + N' between ' + cast(PeriodStartDateKey as nvarchar(8)) + N' and ' + cast(PeriodEndDateKey as nvarchar(8))

	    -- Note that SQL Server knows how to interpret strings in ISO8601 format such as yyyyMMdd, 
	    -- so the code below simply uses the key we generated above
		when N'date' then
           -- for date fields use between as there is no time element
		   DateColumnName + N' between ''' + cast(PeriodStartDateKey as nvarchar(8)) + N''' and ''' + cast(PeriodEndDateKey as nvarchar(8)) + N''''
		when N'datetime' then
			-- for datetime fields, use >= and < date + 1 day as the time element could screw things up
		    DateColumnName + N' >= ''' + cast(PeriodStartDateKey as nvarchar(8)) + N''' and ' + DateColumnName + N' < dateadd(dd,1,''' + cast(PeriodEndDateKey as nvarchar(8)) + N''')'
	end as PartitionWhereClause
FROM SsasPartitions as A;
go
